package com.gsh.service.impl;

import com.gsh.config.JsonTest;
import com.gsh.dao.VoltageDao;
import com.gsh.pojo.Voltage;
import com.gsh.service.IVoltageService;
import com.sun.tools.javac.util.Convert;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class IVoltageServiceImpl implements IVoltageService {

    @Autowired
    VoltageDao voltageDao;

    @Override
    public JsonTest uploadExcel(String fileName, MultipartFile file, JsonTest result) throws IOException {

        InputStream inputStream = file.getInputStream();
        Workbook wb;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            wb = new XSSFWorkbook(inputStream);
        } else {
            wb = new HSSFWorkbook(inputStream);
        }
        Sheet sheet = wb.getSheetAt(0);
        if (sheet == null) {
            result.setCode(400);
            result.setMsg("Excel数据为空！");
            return result;
        }

        // 列数
        int column = sheet.getRow(0).getPhysicalNumberOfCells();
        // 此处可判断Excel列数是否符合要求

        // 行数
        int rows = sheet.getLastRowNum();
        // 此处可判断Excel行数是否符合要求

        List<Voltage> excelData = new ArrayList<>();
        Voltage temporary;

        //循环Excel
        for (int i=1;i<=sheet.getLastRowNum();i++){
            Row row=sheet.getRow(i);
            if(row==null){
                continue;
            }

            temporary=new Voltage();
            if (row.getCell(0) != null) {
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                Date vDate;
                try {
                    vDate = formatter.parse(getCellValue(row.getCell(0)));
                } catch (ParseException e) {
                    result.setCode(400);
                    result.setMsg("Excel中第一项必须是日期！");
                    return result;
                }
                if (vDate == null) {
                    result.setCode(400);
                    result.setMsg("Excel中第一项日期不能为空！");
                    return result;
                }
                //将日期信息写入temporary对象中
                temporary.setVDate(vDate);
            }
            if (row.getCell(1) != null) {
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                Date lDate;
                try {
                    lDate = formatter.parse(getCellValue(row.getCell(1)));
                } catch (ParseException e) {
                    result.setCode(400);
                    result.setMsg("Excel中第一项必须是日期！");
                    return result;
                }
                if (lDate == null) {
                    result.setCode(400);
                    result.setMsg("Excel中第二项日期不能为空！");
                    return result;
                }
                //将日期信息写入temporary对象中
                temporary.setLDate(lDate);
            }
            //写入固定的用户id
            temporary.setVid(3);
            //测量点名称
            if (row.getCell(2) != null) {
                //row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                String MeasuringPoint = getCellValue(row.getCell(2));
                if (MeasuringPoint == null || MeasuringPoint.isEmpty()) {
                    result.setCode(400);
                    result.setMsg("Excel中测量点名称，不能为空，请填写后再进行上传！");
                    return result;
                }
                temporary.setMeasuringPoint(MeasuringPoint);
            }
            //相位
            if (row.getCell(3) != null) {
                //row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                String SErrType = getCellValue(row.getCell(3));
                if (SErrType == null || SErrType.isEmpty()) {
                    result.setCode(400);
                    result.setMsg("Excel中相序，不能为空，请填写后再进行上传！");
                    return result;
                }
                int ErrType=0;
                if(SErrType.startsWith("A")){
                    ErrType=1;
                }else if(SErrType.startsWith("B")){
                    ErrType=2;
                }else if(SErrType.startsWith("C")){
                    ErrType=3;
                }
                temporary.setErrType(ErrType);
            }
            //0-23时
            for (int j=4;j<=27;j++){
                if(row.getCell(j)!=null){
                    //row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                    String SDataValue = getCellValue(row.getCell(j));
                    double VDataValue;//电压数据
                    VDataValue=Double.parseDouble(SDataValue);
                    //将电压信息写入temporary对象中
                    temporary.setVMoment(j-4);//时刻
                    temporary.setVDataValue(VDataValue);//电压数据
                    System.out.println(temporary.toString());
                    Voltage newvoltage=temporary.clone();
                    excelData.add(newvoltage);
                }
            }
        }
        System.out.println(excelData.size());
        if(excelData.size()>0){
            //int i = insertBatchVoltage(excelData);
            int i=excelData.size();
            if(i==excelData.size()){
                result.setCode(200);
                result.setMsg("数据插入成功！");
            }else {
                result.setCode(400);
                result.setMsg("数据未完全插入成功！");
            }
        };
        return result;
    }

    private String getCellValue(Cell cell){
        CellType type = cell.getCellTypeEnum();
        String cellValue = "";
        switch (type) {
            case NUMERIC: // 数字
                // 如果是数字类型的话,判断是不是日期类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date d = cell.getDateCellValue();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = formatter.format(d);
                } else if(cell.getCellStyle().getDataFormat() == 57) {
                    Date d = cell.getDateCellValue();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM");
                    cellValue = formatter.format(d);
                } else {
                    DecimalFormat df = new DecimalFormat("0.00");
                    cellValue = df.format(cell.getNumericCellValue());
                }

                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                try {
                    cellValue = cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
                       /* cellValue = cell.getCellFormula() + "";
                        break;*/
            case BLANK: // 空值
                cellValue = "";
                break;
            case _NONE: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
    @Override
    public int insertVoltage(Voltage voltage) {
        return voltageDao.insertVoltage(voltage);
    }

    @Override
    public int insertBatchVoltage(List<Voltage> voltages) {
        return voltageDao.insertBatchVoltage(voltages);
    }

    @Override
    public List<Voltage> querybyMD(String measuringPoint,int errType, Date beginDate, Date endDate) {
        return voltageDao.querybyMD(measuringPoint,errType,beginDate,endDate);
    }
}
